#!/usr/bin/perl
#####################################################################################
# hindsight.pl                                                                      #
#    Open source Internet history forensics for Google Chrome/Chromium              #
#                                                                                   #
# This script parses the files in the Chrome data folder, runs various plugins      #
#    against the data, and then outputs a spreadsheet in xlsx format.               #
#                                                                                   #
# The Chrome data folder can be found:                                              #
#    WinXP:   <userdir>\Local Settings\Application Data\Google\Chrome               #
#                \User Data\Default\                                                #
#    Vista/7: <userdir>\AppData\Local\Google\Chrome\User Data\Default\              #
#    Linux:   <userdir>/.config/google-chrome/Default/                              #
#    OS X:    <userdir>/Library/Application Support/Google/Chrome/Default/          #
#                                                                                   #
# Additional information/explanations/tutorials can be found on                     #
#    http://www.obsidianforensics.com/blog/                                         #
# Complete source code can be found at                                              #
#    http://code.google.com/p/hindsight-internet-history/                           #
#                                                                                   #
# Released under GNU GPL v3                                                         #
#                                                                                   #
# Author: Ryan Benson (ryan@obsidianforensics.com)                                  #
#     Version: 0.84                                                                 #
#     Date: 7/9/2013                                                                #
#####################################################################################

use strict;
use warnings;
use Excel::Writer::XLSX;
use DBI;
use JSON;
use Time::Local;
use Term::ProgressBar;
use Getopt::Long;
use Data::Dumper;

#####################################################################################
#####                Command line options and initial setup                     #####
#####################################################################################

our $source_dir;
my $base_name;
my $remoteLookupsAllowed;
my $help;
our $version = "0.84";

my $left_width = 42;
my $right_width = 32;

# Get command line flags
my $options = GetOptions (  "i:s" => \$source_dir,   
                            "o:s" => \$base_name,
                            "r"   => \$remoteLookupsAllowed,
							"h"   => \$help	);  

my @startTimeData = localtime(time);
my $startTime = ($startTimeData[5]+1900) . "-" . sprintf("%02s",($startTimeData[4]+1)) . "-" . sprintf("%02s",$startTimeData[3]) . " " . sprintf("%02s",$startTimeData[2]) . "-" . sprintf("%02s",$startTimeData[1]) . "-" . sprintf("%02s",$startTimeData[0]);                

my $db_mode = "normal";

# If help flag is set, display help and exit
if ( $help ){ help(); exit }

# If no input directory and no base name given, display help and exit
if (!$source_dir && !$base_name){ help(); exit }

# If no input directory and base name given, set to rerun mode
if (!$source_dir && $base_name){ $db_mode = "rerun"; }

# If no base name specified, create one based on date/time
unless ($base_name) {
    $base_name = "Hindsight Internet History Analysis ($startTime)";    }

# Attempt to open source dir; die if can't
my @input_listing;
if ($source_dir){
	opendir DIR, "$source_dir" or die "Cannot open input directory: $source_dir: $!";
	@input_listing = readdir DIR;
	closedir DIR;
}

my $counter = 0;

# Define transition description array
# Source: http://src.chromium.org/svn/trunk/src/content/public/common/page_transition_types.h
my @trans_desc = (    "link", "typed", "auto bookmark", "auto subframe", "manual subframe", "generated", "start page", "form submit", "reload", "keyword", "keyword generated", "[error]" );

#####################################################################################
#####                      Pre-processing Status Update                         #####
#####################################################################################

print "Hindsight v$version\n";
print "  Start time: $startTime\n";
if ($source_dir){ print "  Input directory: $source_dir\n"; }
print "  Output name: $base_name\n\n";

#####################################################################################
#####                    Create/Check datastore file                            #####
#####################################################################################

my $datastore_filename = "$base_name.sqlite";
if ( -e $datastore_filename ){    # if file exists 
    if ( -s $datastore_filename ){ # and is >0 bytes
        print "    Database file \"$base_name.sqlite\" already exists.\n    Would you like to (A)dd to it, (O)verwrite it, (R)erun Plugins, or (E)xit?\n";
        $db_mode = <STDIN>;
        if ($db_mode =~ /(^e$|exit)/i ){ exit; }
        elsif ($db_mode =~ /(^o$|overwrite)/i ){
			if ($source_dir){
				$db_mode = "overwrite";
				unlink($datastore_filename); 
				print "  Deleted old $datastore_filename\n";    }
			else { print "  Must enter an input directory (-i); exiting..."; exit; } }
        elsif ($db_mode =~ /(^a$|add)/i ){ 
			if ($source_dir){	$db_mode = "add"; }
			else { print "  Must enter an input directory (-i); exiting..."; exit; } }
		elsif ($db_mode =~ /(^r$|rerun|rerun plugins)/i ){ $db_mode = "rerun"; }
        else { print "    Did not understand response.  Exiting...  "; exit; }
    }
    else { print "    Database file already exists, but is 0 bytes in size.\n"; }
}

our $datastore = DBI->connect("dbi:SQLite:dbname=$datastore_filename", "", "");
if ($datastore->err()) { die "$DBI::errstr\n"; }
my $rid_counter;

unless ($db_mode eq "rerun"){  # Skip all the processing if in rerun mode

#####################################################################################
#####                      Create datastore tables                              #####
#####################################################################################

# if in add mode, find the max existing rid value to set the initial rid_counter
if ($db_mode eq "add"){
    my $max_rid_h = $datastore->prepare("SELECT MAX(rid) FROM rid");
    $max_rid_h->execute();
    $rid_counter = ($max_rid_h->fetchrow) + 1;
    print "  Adding to existing " . ($rid_counter-1) . " records in $datastore_filename\n";
    }

# else create tables in datastore
else {
    print "  Creating tables in $datastore_filename\n";
    my $create_urls_h = $datastore->prepare(
                                "CREATE TABLE urls (
                                        rid INTEGER PRIMARY KEY, 
                                        url_id TEXT, 
                                        url TEXT, 
                                        title TEXT, 
                                        visit_count TEXT, 
                                        typed_count TEXT, 
                                        last_visit_time TEXT, 
                                        hidden TEXT, 
                                        favicon_id TEXT, 
                                        first_visit_time TEXT, 
                                        first_visit_time_human TEXT,
                                        first_visit_time_webkit TEXT, 
                                        from_visit TEXT, 
                                        transition TEXT, 
                                        is_indexed TEXT,
                                        index_data TEXT,
                                        url_root TEXT,
                                        safe_browsing TEXT,
                                        derived TEXT )"  );

    my $create_downloads_h = $datastore->prepare(
                                "CREATE TABLE downloads (
                                        rid INTEGER PRIMARY KEY, 
                                        download_id TEXT, 
                                        url TEXT, 
                                        full_path TEXT, 
                                        start_time TEXT, 
                                        received_bytes TEXT, 
                                        total_bytes TEXT, 
                                        state TEXT,
                                        end_time TEXT,
                                        opened TEXT,
                                        url_root TEXT,
                                        safe_browsing TEXT,
										danger_type TEXT,
                                        interrupt_reason TEXT,
                                        etag TEXT,
										last_modified TEXT,
                                        derived TEXT )"  );

    my $create_autofills_h = $datastore->prepare(
                                "CREATE TABLE autofills (
                                        rid INTEGER PRIMARY KEY,
                                        value TEXT,
                                        name  TEXT,
                                        count TEXT,
                                        date_created TEXT )"  );

    my $create_cookies_h = $datastore->prepare(
                                "CREATE TABLE cookies (
                                        rid INTEGER PRIMARY KEY,
                                        timestamp TEXT,
                                        url TEXT,
                                        name TEXT,
                                        value TEXT,
                                        expires TEXT,
                                        secure TEXT,
                                        httponly TEXT,
                                        derived TEXT )"  );

    my $create_local_storage_h = $datastore->prepare(
                                "CREATE TABLE local_storage (
                                        rid INTEGER PRIMARY KEY,
                                        timestamp TEXT,
                                        url TEXT,
                                        key TEXT,
                                        value TEXT )"  );
                                
    my $create_rid_h = $datastore->prepare(
                                "CREATE TABLE rid (
                                        rid INTEGER PRIMARY KEY,
                                        timestamp TEXT,
                                        record_type TEXT )"  );
										
    my $create_bookmarks_h = $datastore->prepare(
                                "CREATE TABLE bookmarks (
                                        rid INTEGER PRIMARY KEY,
                                        url TEXT,
										name TEXT,
                                        folder TEXT )"  );
										
    my $create_preferences_h = $datastore->prepare(
                                "CREATE TABLE preferences (
                                        option TEXT,
                                        key TEXT,
                                        value TEXT )"  );
                                
    my $create_extensions_h = $datastore->prepare(
                                "CREATE TABLE extensions (
                                        timestamp TEXT,
                                        url TEXT,
                                        key TEXT,
                                        value TEXT )"  );
										
    $create_urls_h->execute();
    $create_downloads_h->execute();
    $create_autofills_h->execute();
    $create_cookies_h->execute();
    $create_local_storage_h->execute();
    $create_rid_h->execute();
	$create_bookmarks_h->execute();
	$create_preferences_h->execute();
	$create_extensions_h->execute();
    $rid_counter = 1;
}

my $update_rid_handler = $datastore->prepare("INSERT INTO rid (    rid, timestamp, record_type    ) VALUES ( ?, ?, ? ) ");

print "  Processing:\n";

#####################################################################################
#####                  Open 'History' file and select fields                    #####
#####################################################################################

my $url_version;

# Define input file
my $historyfile = "$source_dir/History";
if ( -e $historyfile && -s $historyfile){    # if file exists and is >0 btyes

    # Open History SQLite DB
    my $db_history_file = DBI->connect("dbi:SQLite:dbname=$historyfile",,"",,"");
    if ($db_history_file->err()) { die "$DBI::errstr\n"; } 
    
    my $urls_h;

    my $urls_ver_h = $db_history_file->prepare( "PRAGMA table_info(visits)" );  
    $urls_ver_h->execute();

    my @url_row;
    my %url_columns;
    while (@url_row = $urls_ver_h->fetchrow_array()) {
        $url_columns{$url_row[1]} = 1;
    }
    
    if ($url_columns{'is_indexed'}){  # Chrome v1-29
        $url_version = '29';

        # Select all relevant fields for each visit from the History file
        my $history_urls_h = $db_history_file->prepare("SELECT urls.id, 
                                        urls.url, 
                                        urls.title, 
                                        urls.visit_count, 
                                        urls.typed_count, 
                                        urls.last_visit_time, 
                                        urls.hidden, 
                                        urls.favicon_id, 
                                        visits.visit_time, 
                                        visits.from_visit, 
                                        visits.transition,  
                                        visits.is_indexed 
                                    FROM urls, visits WHERE urls.id = visits.url");
        $history_urls_h->execute();

        my $history_urls_count_h = $db_history_file->prepare("SELECT count(*) FROM urls, visits WHERE urls.id = visits.url");
        $history_urls_count_h->execute();
        my $history_urls_count = $history_urls_count_h->fetchrow;

        #Insert the url record data into the url table
        my $insert_row_handler = $datastore->prepare("INSERT INTO urls (
                                        rid,    
                                        url_id, 
                                        url, 
                                        title, 
                                        visit_count, 
                                        typed_count, 
                                        last_visit_time, 
                                        hidden, 
                                        favicon_id,    
                                        first_visit_time, 
                                        first_visit_time_human, 
                                        first_visit_time_webkit,
                                        from_visit, 
                                        transition, 
                                        is_indexed,
                                        url_root   ) 
                                    VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)    ");

        $counter = 0;
        my $url_progress = Term::ProgressBar->new ({count => $history_urls_count, name => sprintf( "%${left_width}s", "URL records ($history_urls_count)") });
        $url_progress->minor(0);

        while (my @row = $history_urls_h->fetchrow) {
            # Convert timestamp from Webkit to unixepoch
            my $visited_epoch = WebkitToEpoch($row[5]);
            my $first_visited_epoch = WebkitToEpoch($row[8]);
            my $first_visited_human = EpochToHuman($first_visited_epoch);
            
            my $url_root = ExtractDomain($row[1]);

            $insert_row_handler->execute($rid_counter, $row[0], $row[1], $row[2], $row[3], $row[4], $visited_epoch, $row[6], $row[7], $first_visited_epoch, $first_visited_human, $row[8], $row[9], DecodeTransition($row[10]), $row[11], $url_root);
            $update_rid_handler->execute($rid_counter, $first_visited_epoch, "url" );
            $rid_counter++;
            
            $counter++;
            $url_progress->update($counter);
        }
    }

    else { # Chrome v30+
        $url_version = '30';

        # Select all relevant fields for each visit from the History file
        my $history_urls_h = $db_history_file->prepare("SELECT urls.id, 
                                        urls.url, 
                                        urls.title, 
                                        urls.visit_count, 
                                        urls.typed_count, 
                                        urls.last_visit_time, 
                                        urls.hidden, 
                                        urls.favicon_id, 
                                        visits.visit_time,
                                        visits.from_visit, 
                                        visits.transition  
                                    FROM urls, visits WHERE urls.id = visits.url");
        $history_urls_h->execute();

        my $history_urls_count_h = $db_history_file->prepare("SELECT count(*) FROM urls, visits WHERE urls.id = visits.url");
        $history_urls_count_h->execute();
        my $history_urls_count = $history_urls_count_h->fetchrow;

        #Insert the url record data into the url table
        my $insert_row_handler = $datastore->prepare("INSERT INTO urls (
                                        rid,    
                                        url_id, 
                                        url, 
                                        title, 
                                        visit_count, 
                                        typed_count, 
                                        last_visit_time, 
                                        hidden, 
                                        favicon_id,    
                                        first_visit_time, 
                                        first_visit_time_human, 
                                        first_visit_time_webkit,
                                        from_visit, 
                                        transition, 
                                        url_root   ) 
                                    VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)    ");

        $counter = 0;
        my $url_progress = Term::ProgressBar->new ({count => $history_urls_count, name => sprintf( "%${left_width}s", "URL records ($history_urls_count)") });
        $url_progress->minor(0);

        while (my @row = $history_urls_h->fetchrow) {
            # Convert timestamp from Webkit to unixepoch
            my $visited_epoch = WebkitToEpoch($row[5]);
            my $first_visited_epoch = WebkitToEpoch($row[8]);
            my $first_visited_human = EpochToHuman($first_visited_epoch);
            
            my $url_root = ExtractDomain($row[1]);

            $insert_row_handler->execute($rid_counter, $row[0], $row[1], $row[2], $row[3], $row[4], $visited_epoch, $row[6], $row[7], $first_visited_epoch, $first_visited_human, $row[8], $row[9], DecodeTransition($row[10]), $url_root);
            $update_rid_handler->execute($rid_counter, $first_visited_epoch, "url" );
            $rid_counter++;
            
            $counter++;
            $url_progress->update($counter);
        }

    }
    print "\n";

    # Process download information from the same History file
    # Select all relevant fields for each visit from the History file

    my $downloads_h;
    my $dl_version;

	my $downloads_ver_h = $db_history_file->prepare( "PRAGMA table_info(downloads)" );  
	$downloads_ver_h->execute();

	my @dl_row;
	my %dl_columns;
	while (@dl_row = $downloads_ver_h->fetchrow_array()) {
		$dl_columns{$dl_row[1]} = 1;
	}
	
	my $chrome_version;
    if ($dl_columns{'etag'}){  # Chrome v30+
        $chrome_version = '30';
        $downloads_h = $db_history_file->prepare("SELECT downloads.id, 
                                downloads.target_path, 
                                downloads.start_time, 
                                downloads.received_bytes,
                                downloads.total_bytes,
                                downloads.state,
                                downloads.end_time,
                                downloads.opened,
                                downloads.danger_type,
                                downloads.interrupt_reason,
                                downloads.etag,
                                downloads.last_modified
                            FROM downloads");
    }

	elsif ($dl_columns{'danger_type'}){  # Chrome v26-29
        $chrome_version = '26';
		$downloads_h = $db_history_file->prepare("SELECT downloads.id, 
                                downloads.target_path, 
                                downloads.start_time, 
                                downloads.received_bytes,
                                downloads.total_bytes,
                                downloads.state,
                                downloads.end_time,
                                downloads.opened,
								downloads.danger_type,
								downloads.interrupt_reason
                            FROM downloads");
                            # FROM downloads, downloads_url_chains WHERE downloads.id = downloads_url_chains.id");
	}

	elsif ($dl_columns{'end_time'}){  # Chrome v16-25
		$chrome_version = '16';
        $downloads_h = $db_history_file->prepare("SELECT downloads.id, 
                                downloads.full_path, 
                                downloads.url, 
                                downloads.start_time, 
                                downloads.received_bytes,
                                downloads.total_bytes,
                                downloads.state,
                                downloads.end_time,
                                downloads.opened
                            FROM downloads");
	}

	else {  # Chrome v1-15
		$chrome_version = '1';
        $downloads_h = $db_history_file->prepare("SELECT downloads.id, 
                                downloads.full_path, 
                                downloads.url, 
                                downloads.start_time, 
                                downloads.received_bytes,
                                downloads.total_bytes,
                                downloads.state
                            FROM downloads");
	}

	$downloads_h->execute();
	
    my $downloads_count_h = $db_history_file->prepare("SELECT count(*) FROM downloads");
    $downloads_count_h->execute();
    my $downloads_count = $downloads_count_h->fetchrow;

    $counter = 0;
    my $downloads_progress = Term::ProgressBar->new ({count => $downloads_count, name => sprintf "%${left_width}s", "Download records ($downloads_count)"});
    $downloads_progress->minor(0);


    if ($chrome_version >= '26'){
        my $insert_downloads_handler = $datastore->prepare("INSERT INTO downloads (    
                                    rid,    
                                    download_id, 
                                    full_path,
                                    url,
                                    start_time, 
                                    received_bytes,  
                                    total_bytes, 
                                    state,
                                    end_time,
                                    opened,
                                    url_root,
    								danger_type,
    								interrupt_reason,
                                    etag,
                                    last_modified,
                                    derived ) 
                                VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)    ");

        my $download_chain_h = $db_history_file->prepare("SELECT downloads_url_chains.chain_index, downloads_url_chains.url FROM downloads_url_chains WHERE downloads_url_chains.id =? ORDER BY downloads_url_chains.chain_index DESC");
          
        #Insert the download record data into the all_entries table
        while (my @row = $downloads_h->fetchrow) {
            my $download_url;
            my $download_chain_text;
            my $number_of_links;

            $download_chain_h->execute($row[0]);
            my @last_chain = $download_chain_h->fetchrow;
            $download_url = $last_chain[1];
            $download_chain_text = $download_url;
            $number_of_links = $last_chain[0];

            if ($number_of_links > 0){
                while (my @chain_row = $download_chain_h->fetchrow){
                    $download_chain_text = $chain_row[1] . " -> " . $download_chain_text;
                }
                $download_chain_text = "Download Chain: " . $download_chain_text;
            }

            my $url_root = ExtractDomain($row[2]);
            $insert_downloads_handler->execute($rid_counter, $row[0], $row[1], $download_url, &WebkitToEpoch($row[2]), $row[3], $row[4], $row[5], &WebkitToEpoch($row[6]), $row[7], $url_root, $row[8], $row[9], ($chrome_version >= '30') ? $row[10] : "", ($chrome_version >= '30') ? $row[11] : "", ($number_of_links > 0 ) ? $download_chain_text : "");
            $update_rid_handler->execute($rid_counter, &WebkitToEpoch($row[2]), "download" );
            $rid_counter++;

            $counter++;
            $downloads_progress->update($counter);
        }
    }

    else { # Chrome version 25 and below (pre-download chains)
        my $insert_downloads_handler = $datastore->prepare("INSERT INTO downloads (    
                                    rid,    
                                    download_id, 
                                    full_path,
                                    url,  
                                    start_time, 
                                    received_bytes,  
                                    total_bytes, 
                                    state,
                                    end_time,
                                    opened,
                                    url_root,
                                    danger_type,
                                    interrupt_reason,
                                    etag,
                                    last_modified ) 
                                VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)    ");
    
        #Insert the download record data into the all_entries table
        while (my @row = $downloads_h->fetchrow) {
            #my $visited_human = EpochToHuman($row[3]);

            my $url_root = ExtractDomain($row[2]);
            $insert_downloads_handler->execute($rid_counter, $row[0], $row[1], $row[2], &WebkitToEpoch($row[3]), $row[4], $row[5], $row[6], &WebkitToEpoch($row[6]), $row[8], $url_root,($chrome_version >= '26') ? $row[9] : "", ($chrome_version >= '26') ? $row[10] : "", ($chrome_version >= '30') ? $row[11] : "", ($chrome_version >= '30') ? $row[12] : "");
            $update_rid_handler->execute($rid_counter, &WebkitToEpoch($row[3]), "download" );
            $rid_counter++;

            $counter++;
            $downloads_progress->update($counter);
        }
    }















print "\n";
} else { printDescription('URL records (0)');
          print ":    ";
           printStatus('file not found'); 
            printDescription('Download records (0)');
             print ":    ";
              printStatus('file not found'); }


#####################################################################################
#####              Open 'Archived History' file and select fields               #####
#####################################################################################

# Check input file
my $archivehistoryfile = "$source_dir/Archived History";
if ( -e $archivehistoryfile && -s $archivehistoryfile){        # if file exists and is >0 btyes
    my $db_archived_history_file = DBI->connect("dbi:SQLite:dbname=$archivehistoryfile",,"",,"");
    if ($db_archived_history_file->err()) { die "$DBI::errstr\n"; } 

    if ($url_version == '29'){
        my $archive_history_handler = $db_archived_history_file->prepare("SELECT urls.id, 
                                        urls.url, 
                                        urls.title, 
                                        urls.visit_count, 
                                        urls.typed_count, 
                                        urls.last_visit_time, 
                                        urls.hidden, 
                                        urls.favicon_id, 
                                        visits.visit_time, 
                                        visits.from_visit, 
                                        visits.transition,  
                                        visits.is_indexed 
                                    FROM urls, visits WHERE urls.id = visits.url");
        $archive_history_handler->execute();
        
        my $archived_history_urls_count_h = $db_archived_history_file->prepare("SELECT count(*) FROM urls, visits WHERE urls.id = visits.url");
        $archived_history_urls_count_h->execute();
        my $archived_history_urls_count = $archived_history_urls_count_h->fetchrow;
        
        my $archive_insert_row_handler = $datastore->prepare("INSERT INTO urls (
                                        rid,    
                                        url_id, 
                                        url, 
                                        title, 
                                        visit_count, 
                                        typed_count, 
                                        last_visit_time, 
                                        hidden, 
                                        favicon_id,    
                                        first_visit_time, 
                                        first_visit_time_human, 
                                        first_visit_time_webkit,
                                        from_visit, 
                                        transition, 
                                        is_indexed,
                                        url_root  ) 
                                    VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  ");

        $counter = 0;
    
        my $archived_url_progress = Term::ProgressBar->new ({count => $archived_history_urls_count, name => sprintf "%${left_width}s", "Archived URL records ($archived_history_urls_count)"});
        $archived_url_progress->minor(0);

        while (my @row = $archive_history_handler->fetchrow) {
            # Convert timestamp from Webkit to unixepoch
            my $archive_visited_epoch = WebkitToEpoch($row[5]);
            my $archive_first_visited_epoch = WebkitToEpoch($row[8]);
            my $archive_first_visited_human = EpochToHuman($archive_first_visited_epoch);

            my $url_root = ExtractDomain($row[1]);

            $archive_insert_row_handler->execute($rid_counter, $row[0], $row[1], $row[2], $row[3], $row[4], $archive_visited_epoch, $row[6], $row[7], $archive_first_visited_epoch, $archive_first_visited_human, $row[8], $row[9], DecodeTransition($row[10]), $row[11], $url_root);
            $update_rid_handler->execute($rid_counter, $archive_first_visited_epoch, "url (archived)" );
            $rid_counter++;

            $counter++;
            $archived_url_progress->update($counter);
        }
    }

    else { # Chrome v30+
        my $archive_history_handler = $db_archived_history_file->prepare("SELECT urls.id, 
                                        urls.url, 
                                        urls.title, 
                                        urls.visit_count, 
                                        urls.typed_count, 
                                        urls.last_visit_time, 
                                        urls.hidden, 
                                        urls.favicon_id, 
                                        visits.visit_time, 
                                        visits.from_visit, 
                                        visits.transition  
                                    FROM urls, visits WHERE urls.id = visits.url");
        $archive_history_handler->execute();
        
        my $archived_history_urls_count_h = $db_archived_history_file->prepare("SELECT count(*) FROM urls, visits WHERE urls.id = visits.url");
        $archived_history_urls_count_h->execute();
        my $archived_history_urls_count = $archived_history_urls_count_h->fetchrow;
        
        my $archive_insert_row_handler = $datastore->prepare("INSERT INTO urls (
                                        rid,    
                                        url_id, 
                                        url, 
                                        title, 
                                        visit_count, 
                                        typed_count, 
                                        last_visit_time, 
                                        hidden, 
                                        favicon_id,    
                                        first_visit_time, 
                                        first_visit_time_human, 
                                        first_visit_time_webkit,
                                        from_visit, 
                                        transition, 
                                        url_root  ) 
                                    VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  ");
        $counter = 0;
    
        my $archived_url_progress = Term::ProgressBar->new ({count => $archived_history_urls_count, name => sprintf "%${left_width}s", "Archived URL records ($archived_history_urls_count)"});
        $archived_url_progress->minor(0);

        while (my @row = $archive_history_handler->fetchrow) {
            # Convert timestamp from Webkit to unixepoch
            my $archive_visited_epoch = WebkitToEpoch($row[5]);
            my $archive_first_visited_epoch = WebkitToEpoch($row[8]);
            my $archive_first_visited_human = EpochToHuman($archive_first_visited_epoch);

            my $url_root = ExtractDomain($row[1]);

            $archive_insert_row_handler->execute($rid_counter, $row[0], $row[1], $row[2], $row[3], $row[4], $archive_visited_epoch, $row[6], $row[7], $archive_first_visited_epoch, $archive_first_visited_human, $row[8], $row[9], DecodeTransition($row[10]), $url_root);
            $update_rid_handler->execute($rid_counter, $archive_first_visited_epoch, "url (archived)" );
            $rid_counter++;

            $counter++;
            $archived_url_progress->update($counter);
        }

    }


print "\n";
} else { printDescription('Archived URL records (0)');
          print ":    ";
           printStatus('file not found'); }

#####################################################################################
#####              Open 'History Index' files and select fields                 #####
#####################################################################################

# Get count of History Index files (for progress bar)
my $history_index_count = 0;
foreach my $index_file (@input_listing) {
    if ( $index_file=~/History Index \d{4}-\d{2}$/){
         $history_index_count++;    }    }

# Define input files
if ($history_index_count > 0){
    $counter = 0;
    my $history_index_progress = Term::ProgressBar->new ({count => $history_index_count, name => sprintf "%${left_width}s", "History Index files ($history_index_count)"});
    $history_index_progress->minor(0);
    
    foreach my $index_file (@input_listing) {
        if ( $index_file=~/History Index \d{4}-\d{2}$/){
            my $historyindexfile = "$source_dir/$index_file";
            if ( -e $historyindexfile && -s $historyindexfile){
                my $db_historyindex_file = DBI->connect("dbi:SQLite:dbname=$historyindexfile",,"",,"");
                if ($db_historyindex_file->err()) { die "$DBI::errstr\n"; } 

                my $indexed_content_handler = $db_historyindex_file->prepare("SELECT c2body FROM pages_content JOIN info ON pages_content.rowid=info.rowid WHERE time=? AND c0url=?");
                my $insert_indexed_content_handler = $datastore->prepare("UPDATE urls SET index_data=? WHERE first_visit_time_webkit=? AND url=?");
                
                # Get list of urls that are indexed
                my $indexed_urls_handler = $datastore->prepare("SELECT url,first_visit_time_webkit FROM urls WHERE is_indexed = '1'");
                $indexed_urls_handler->execute();

                # For each indexed url
                while ( my @url = $indexed_urls_handler->fetchrow ){
                    # Get the indexed data
                    $indexed_content_handler->execute($url[1], $url[0]);
                    while ( my @indexed_content = $indexed_content_handler->fetchrow ){
                        # And insert it in the main table
                        $insert_indexed_content_handler->execute($indexed_content[0],$url[1], $url[0]);
                    }
                }
            }
        $counter++;
        $history_index_progress->update($counter);
        }
    }
print "\n";
} else { printDescription('History Index Files (0)');
          print ":    ";
           printStatus('file not found'); }

#####################################################################################
#####                  Open 'Web Data' file and select fields                   #####
#####################################################################################

# Define input files
my $webdatafile = "$source_dir/Web Data";
if ( -e $webdatafile && -s $webdatafile){
    my $db_webdata_file = DBI->connect("dbi:SQLite:dbname=$webdatafile",,"",,"");
    if ($db_webdata_file->err()) { die "$DBI::errstr\n"; } 

    my $webdata_handler = $db_webdata_file->prepare(
                           "SELECT autofill.value, 
                                    autofill.name,
                                    autofill.count,
                                    autofill_dates.date_created
                            FROM autofill, autofill_dates WHERE autofill.pair_id = autofill_dates.pair_id");
    $webdata_handler->execute();

    my $webdata_count_handler = $db_webdata_file->prepare("SELECT count(*) FROM autofill, autofill_dates WHERE autofill.pair_id = autofill_dates.pair_id");
    $webdata_count_handler->execute();
    my $webdata_count = $webdata_count_handler->fetchrow;

    my $webdata_import_handler = $datastore->prepare("INSERT INTO autofills ( 
                                                        rid,    
                                                        value, 
                                                        name, 
                                                        count, 
                                                        date_created  ) 
                                                      VALUES ( ?, ?, ?, ?, ? )");

    $counter = 0;

    my $webdata_progress = Term::ProgressBar->new ({count => $webdata_count, name => sprintf "%${left_width}s", "Autofill records ($webdata_count)"});
    $webdata_progress->minor(0);

    while (my @row = $webdata_handler->fetchrow) {
        $webdata_import_handler->execute($rid_counter, $row[0], $row[1], $row[2], $row[3] );
        $update_rid_handler->execute($rid_counter, $row[3], "autofill" );
        $rid_counter++;

        $counter++;
        $webdata_progress->update($counter);
    }
    print "\n";
} else { printDescription('Autofill records (0)');
          print ":    ";
           printStatus('file not found'); }

#####################################################################################
#####                  Open 'Cookies' file and select fields                    #####
#####################################################################################

# Define input files
my $cookiesfile = "$source_dir/Cookies";
if ( -e $cookiesfile && -s $cookiesfile){    
    my $db_cookies_file = DBI->connect("dbi:SQLite:dbname=$cookiesfile",,"",,"");
    if ($db_cookies_file->err()) { die "$DBI::errstr\n"; } 

    my $cookies_handler = $db_cookies_file->prepare(
                            "SELECT cookies.creation_utc,
                                    cookies.last_access_utc,
                                    cookies.host_key,
                                    cookies.name,
                                    cookies.value,
                                    cookies.path,
                                    cookies.expires_utc,
                                    cookies.secure,
                                    cookies.httponly
                            FROM cookies");
    $cookies_handler->execute();

    my $cookies_count_h = $db_cookies_file->prepare("SELECT count(*) FROM cookies");
    $cookies_count_h->execute();
    my $cookies_count = $cookies_count_h->fetchrow;

    my $cookies_import_handler = $datastore->prepare("INSERT INTO cookies (    
                                                        rid,    
                                                        timestamp, 
                                                        url, 
                                                        name, 
                                                        value,
                                                        expires,
                                                        secure,
                                                        httponly    ) 
                                                    VALUES    ( ?, ?, ?, ?, ?, ?, ?, ? )");

    my $cookie_progress = Term::ProgressBar->new ({count => $cookies_count, name => sprintf "%${left_width}s", "Cookie records ($cookies_count)"});
    $cookie_progress->minor(0);
    
    $counter = 0;
    while (my @row = $cookies_handler->fetchrow) {
        my $cookie_url = $row[2] . $row[5];
        my $cookie_created_epoch = WebkitToEpoch($row[0]);
        $cookies_import_handler->execute($rid_counter, $row[0], $cookie_url, $row[3], $row[4], $row[6], $row[7], $row[8] );
        $update_rid_handler->execute($rid_counter, $cookie_created_epoch, "cookie (created)" );
        $rid_counter++;

        my $cookie_accessed_epoch = WebkitToEpoch($row[1]);
        $cookies_import_handler->execute($rid_counter, $row[1], $cookie_url, $row[3], $row[4], $row[6], $row[7], $row[8] );
        $update_rid_handler->execute($rid_counter, $cookie_accessed_epoch, "cookie (accessed)" );
        $rid_counter++;
        
        $counter++;
        $cookie_progress->update($counter);
    }
    print "\n";
} else {     printDescription('Cookie records (0)');
             print ":    ";
              printStatus('file not found'); }
              
#####################################################################################
#####               Open 'Local Storage' files and select fields                #####
#####################################################################################

# Define input files
if ( -d "$source_dir/Local Storage"){
    opendir DIR, "$source_dir/Local Storage" or die "cannot open dir $source_dir: $!";
    my @local_listing = readdir DIR;
    closedir DIR;

    my $local_storage_import_handler = $datastore->prepare("INSERT INTO local_storage (
                                                                rid,
                                                                timestamp,
                                                                url,
                                                                key,
                                                                value )
                                                            VALUES ( ?, ?, ?, ?, ? )");

    my $local_storage_count = 0;
    foreach my $local_file (@local_listing) {
        if ( $local_file =~ /http/ && $local_file !~ /-journal$/ ){ $local_storage_count++; }    }
    
    $counter = 0;

    if ($local_storage_count > 0){
        my $local_storage_progress = Term::ProgressBar->new ({count => $local_storage_count, name => sprintf "%${left_width}s", "Local Storage files ($local_storage_count)"});
        $local_storage_progress->minor(0);

        foreach my $local_file (@local_listing) {
            if ( $local_file =~ /http/ && $local_file !~ /-journal$/){
                my $local_storage_file = "$source_dir/Local Storage/$local_file";
                my @stat = stat($local_storage_file);
                my $db_local_storage_file = DBI->connect("dbi:SQLite:dbname=$local_storage_file",,"",,"");
				#$db_local_storage_file->{PrintError} = 0;
                my $local_storage_handler = $db_local_storage_file->prepare("SELECT key,value FROM ItemTable");
				#$db_local_storage_file->{PrintError} = 1;
				unless ($db_local_storage_file->err()) { 
					$local_storage_handler->execute();
					
					# For each key/value in the local storage file
					while ( my @pair = $local_storage_handler->fetchrow ){
						# Insert data into local_storage table
						unless ($pair[1]) { $pair[1] = " "; }
						$local_storage_import_handler->execute($rid_counter,$stat[9],$local_file,$pair[0],$pair[1]);
						$update_rid_handler->execute($rid_counter, $stat[9], "local storage" );
						$rid_counter++;
					}
				}
            $counter++;
            $local_storage_progress->update($counter);
            }
        }
        print "\n";
    }
} else {    printDescription('Local Storage records (0)');
             print ":    ";
              printStatus('directory not found'); }

#####################################################################################
#####                  Open 'Bookmarks' file and select fields                  #####
#####################################################################################

# Define input files
my $bookmarksfile = "$source_dir/Bookmarks";
my $bookmark_counter = 0;
my $bookmarks_import_handler = $datastore->prepare("INSERT INTO bookmarks (
														rid,
														url,
														name,
														folder )
													VALUES ( ?, ?, ?, ? )");

if ( -e $bookmarksfile && -s $bookmarksfile){  
	my $bookmark_data;
	if (open (my $bookmarks_json, $bookmarksfile)){
		local $/ = undef;
		my $json = JSON->new;
		$bookmark_data = $json->decode(<$bookmarks_json>);
		close($bookmarks_json);
	}

	foreach my $top_level (keys %{$bookmark_data->{'roots'}}){
		if ($top_level !~ /^(synced|meta_info)$/){
			foreach my $second_level (keys %{$bookmark_data->{'roots'}->{$top_level}}){
				if ($second_level eq "children"){
					&process_bookmark_children($bookmark_data->{'roots'}->{$top_level}->{'name'}, @{$bookmark_data->{'roots'}->{$top_level}->{'children'}});
				}
			}
		}
	}
	
	printDescription("Bookmarks ($bookmark_counter)");
    print ":    ";
    printStatus('Bookmarks file parsed');
	
} else {    printDescription('Bookmarks (0)');
             print ":    ";
              printStatus('file not found'); }

sub process_bookmark_children {
	my $parent = shift;
	my (@children)= @_;
	
	foreach my $child (@children){
		if ($child->{'type'} eq "url"){
			$bookmarks_import_handler->execute($rid_counter, $child->{'url'}, $child->{'name'}, "In Bookmark folder: $parent" );
			$update_rid_handler->execute($rid_counter, &WebkitToEpoch($child->{'date_added'}), "new bookmark" );
			$rid_counter++;	
			$bookmark_counter++;
		}

		elsif ($child->{'type'} eq "folder"){
			my $folder_name = $parent . " > " . $child->{'name'};

			$bookmarks_import_handler->execute($rid_counter, "", "Created Bookmark Folder \'$child->{'name'}\'", "In Bookmark folder: $parent" );
			$update_rid_handler->execute($rid_counter, &WebkitToEpoch($child->{'date_added'}), "bookmark folder" );
			$rid_counter++;					
			
			&process_bookmark_children($folder_name, @{$child->{'children'}});
		}
	}
}

} # end rerun
			  
#####################################################################################
#####                              Run Plugins                                  #####
#####################################################################################

# Attempt to open plugin dir
if ( -d "plugins"){
    print "\n  Running Plugins:\n";
    opendir DIR, "plugins" or print "    Cannot open plugin directory ($!)\n";
    my @plugins = readdir DIR;
    closedir DIR;
    
    foreach my $plugin (@plugins) {
        if ( $plugin =~ /.pl$/ ){ 
            $plugin =~ s/\.pl$//;
            eval {
                require "plugins/$plugin.pl";
                my $version = $plugin->getVersion();
                my $friendlyName = $plugin->getFriendlyName();
                my $out = sprintf( "%${left_width}s", "$friendlyName (v$version)");
                print "$out:     ";
                my $pluginRemoteLookups = $plugin->getRemoteLookups();
                unless ($pluginRemoteLookups && !$remoteLookupsAllowed){
                    $plugin->pluginmain();    }
                else{  printStatus("remote lookups disabled");    }
            };    
        }        
    }
} else { print "\n  Plugins directory not found\n"; }

#####################################################################################
#####                          Create output file                               #####
#####################################################################################

print "\n  Creating output file\n";

# Create file
my $workbook = Excel::Writer::XLSX->new( "$base_name.xlsx" );

# Define cell formats
my $title_header_format = $workbook->add_format(    color => "white",                      bg_color => "gray",     bold => 1,  );
my $center_header_format= $workbook->add_format(    color => "black",  align => "center",  bg_color => "gray",     bold => 1,  );
my $header_format       = $workbook->add_format(    color => "black",                      bg_color => "gray",     bold => 1,  );

my $black_type_format   = $workbook->add_format(    color => "black",  align => "left",                                        );
my $black_date_format   = $workbook->add_format(    color => "black",                      num_format => 'mm/dd/yyyy hh:mm:ss' );
my $black_url_format    = $workbook->add_format(    color => "black",  align => "left",                                        );
my $black_field_format  = $workbook->add_format(    color => "black",  align => "left",                                        );
my $black_value_format  = $workbook->add_format(    color => "black",  align => "left",    num_format => 0,                    );
my $black_flag_format   = $workbook->add_format(    color => "black",  align => "center",                                      );
my $black_trans_format  = $workbook->add_format(    color => "black",  align => "left",                                        );

my $gray_type_format    = $workbook->add_format(    color => "gray",   align => "left",                                        );
my $gray_date_format    = $workbook->add_format(    color => "gray",                       num_format => 'mm/dd/yyyy hh:mm:ss' );
my $gray_url_format     = $workbook->add_format(    color => "gray",   align => "left",                                        );
my $gray_field_format   = $workbook->add_format(    color => "gray",   align => "left",                                        );
my $gray_value_format   = $workbook->add_format(    color => "gray",   align => "left",    num_format => 0,                    );
    
my $red_type_format     = $workbook->add_format(    color => "red",    align => "left",                                        );
my $red_date_format     = $workbook->add_format(    color => "red",                        num_format => 'mm/dd/yyyy hh:mm:ss' );
my $red_url_format      = $workbook->add_format(    color => "red",    align => "left",                                        );
my $red_field_format    = $workbook->add_format(    color => "red",    align => "right",                                       );
my $red_value_format    = $workbook->add_format(    color => "red",    align => "left",    num_format => 0,                    );

my $green_type_format   = $workbook->add_format(    color => "green",  align => "left",                                        );
my $green_date_format   = $workbook->add_format(    color => "green",                      num_format => 'mm/dd/yyyy hh:mm:ss' );
my $green_url_format    = $workbook->add_format(    color => "green",  align => "left",                                        );
my $green_field_format  = $workbook->add_format(    color => "green",  align => "left",                                        );
my $green_value_format  = $workbook->add_format(    color => "green",  align => "left",                                        );


# Add main tab
my $worksheet = $workbook->add_worksheet('Activity');

# Title bar
$worksheet->merge_range( 'A1:G1', "Hindsight Internet History Forensics (v$version)",    $title_header_format );
$worksheet->merge_range( 'H1:K1', 'URL Specific',                                        $center_header_format );
$worksheet->merge_range( 'L1:P1', 'Download Specific',                                   $center_header_format );

# Write column headers
$worksheet->write( 1, 0, "Type",                                                         $header_format );
$worksheet->write( 1, 1, "Timestamp",                                                    $header_format );
$worksheet->write( 1, 2, "URL",                                                          $header_format );
$worksheet->write_rich_string( 1, 3, "Title / Name / Status",                            $header_format );
$worksheet->write_rich_string( 1, 4, "Data / Value / Path",                              $header_format );
$worksheet->write( 1, 5, "Interpretation",                                               $header_format );
$worksheet->write( 1, 6, "Safe?",                                                        $header_format );
$worksheet->write( 1, 7, "Visit Count",                                                  $header_format );
$worksheet->write( 1, 8, "Typed Count",                                                  $header_format );
$worksheet->write( 1, 9, "URL Hidden",                                                   $header_format );
$worksheet->write( 1, 10, "Transition",                                                  $header_format );
$worksheet->write( 1, 11, "Interrupt Reason",                                            $header_format );
$worksheet->write( 1, 12, "Danger Type",                                                 $header_format );
$worksheet->write( 1, 13, "Opened?",                                                     $header_format );
$worksheet->write( 1, 14, "ETag",                                                        $header_format );
$worksheet->write( 1, 15, "Last Modified",                                               $header_format );

# Get count of number of rows to write out
my $count_rows_for_excel = $datastore->prepare("select count(*) from rid");
$count_rows_for_excel->execute();
my $excel_count = $count_rows_for_excel->fetchrow;
$excel_count = $excel_count +2;

# Creating spreadsheet progess bar
my $excel_progress = Term::ProgressBar->new ({count => $excel_count, name => sprintf "%${left_width}s", "Writing rows to spreadsheet ($excel_count)"});
$excel_progress->minor(0);

# Select all rows to fill out table
my $rows_for_excel = $datastore->prepare("SELECT * FROM rid
                                            LEFT JOIN urls 
                                              ON rid.rid = urls.rid
                                            LEFT JOIN downloads 
                                              ON rid.rid = downloads.rid
                                            LEFT JOIN autofills 
                                              ON rid.rid = autofills.rid
                                            LEFT JOIN cookies 
                                              ON rid.rid = cookies.rid    
                                            LEFT JOIN local_storage 
                                              ON rid.rid = local_storage.rid
											LEFT JOIN bookmarks 
                                              ON rid.rid = bookmarks.rid
                                            ORDER BY timestamp ASC
                                        ");
$rows_for_excel->execute();

# Write all rows to Excel table
my $row_number = 2;
while (my @row = $rows_for_excel->fetchrow) {
    if ( $row[2] =~ /url/ ){
        $worksheet->write_string( $row_number, 0, $row[2],                $black_type_format  );    # record_type
        $worksheet->write(        $row_number, 1, &EpochToExcel($row[1]), $black_date_format  );    # date
        $worksheet->write_string( $row_number, 2, $row[5],                $black_url_format   );    # URL
        $worksheet->write_string( $row_number, 3, $row[6],                $black_field_format );    # Title
        $worksheet->write(        $row_number, 4, $row[18],               $black_value_format );    # Indexed Content
        $worksheet->write(        $row_number, 5, $row[21],               $black_value_format );    # Interpretation
        $worksheet->write(        $row_number, 6, $row[20],               $black_type_format  );    # Safe Browsing
        $worksheet->write(        $row_number, 7, $row[7],                $black_flag_format  );    # Visit Count
        $worksheet->write(        $row_number, 8, $row[8],                $black_flag_format  );    # Typed Count
        $worksheet->write(        $row_number, 9, $row[10],               $black_flag_format  );    # Hidden
        $worksheet->write_string( $row_number, 10, $trans_desc[$row[16]], $black_trans_format );    # Transition
    }

    if ( $row[2] =~ /autofill/ ){
        $worksheet->write_string( $row_number, 0, $row[2],                $red_type_format    );    # record_type
        $worksheet->write(        $row_number, 1, &EpochToExcel($row[1]), $red_date_format    );    # date
        $worksheet->write_string( $row_number, 3, $row[41],               $red_field_format   );    # autofill field
        $worksheet->write_string( $row_number, 4, $row[40],               $red_value_format   );    # autofill value
        $worksheet->write_string( $row_number, 6, " ",                    $red_type_format    );    # blank
    }
    
    if ( $row[2] =~ /download/ ){
        my $dl_percent;
        if ( $row[28] != 0){ 
            $dl_percent = ( $row[27]/$row[28] * 100) % 1000; }
        else { $dl_percent = "?"; }

        $worksheet->write_string( $row_number, 0,  $row[2],                $green_type_format  );    # record_type
        $worksheet->write(        $row_number, 1,  &EpochToExcel($row[1]), $green_date_format  );    # date
        $worksheet->write_string( $row_number, 2,  $row[24],               $green_url_format   );    # download URL
        $worksheet->write_string( $row_number, 3,  &DecodeDownloadState($row[29]) . " - $dl_percent% [$row[27]/$row[28]]", $green_field_format ); # bytes_received / bytes_total
        $worksheet->write_string( $row_number, 4,  $row[25],               $green_value_format );    # download path
        $worksheet->write_string( $row_number, 5,  $row[38],               $green_field_format );    # DL state, interrupt, danger
        $worksheet->write(        $row_number, 6,  $row[33],               $green_type_format  );    # Safe Browsing
        $worksheet->write_string( $row_number, 11, &DecodeInterruptReason($row[35]),  $green_value_format );    # download path
        $worksheet->write_string( $row_number, 12, &DecodeDangerType($row[34]),  $green_value_format );    # download path
        $worksheet->write_string( $row_number, 13, ($row[31] == '1') ? "Yes" : "No",  $green_value_format );    # opened
        $worksheet->write_string( $row_number, 14, $row[36],               $green_value_format );    # ETag
        $worksheet->write_string( $row_number, 15, $row[37],               $green_value_format );    # Last Modified
    }

    if ( $row[2] =~ /cookie/ ){
        $worksheet->write_string( $row_number, 0, $row[2],                $gray_type_format   );    # record_type
        $worksheet->write(        $row_number, 1, &EpochToExcel($row[1]), $gray_date_format   );    # date
        $worksheet->write_string( $row_number, 2, "    $row[46]",         $gray_url_format    );    # URL
        $worksheet->write_string( $row_number, 3, $row[47],               $gray_field_format  );    # cookie name
        $worksheet->write_string( $row_number, 4, $row[48],               $gray_value_format  );    # cookie value
        $worksheet->write(        $row_number, 5, $row[52],               $gray_value_format  );    # cookie interpretation
        $worksheet->write_string( $row_number, 6, " ",                    $gray_type_format   );    # blank
    }

    if ( $row[2] =~ /local storage/ ){
        $worksheet->write_string( $row_number, 0, $row[2],                $gray_type_format   );    # record_type
        $worksheet->write(        $row_number, 1, &EpochToExcel($row[1]), $gray_date_format   );    # date
        $worksheet->write_string( $row_number, 2, "    $row[55]",         $gray_url_format    );    # URL
        $worksheet->write_string( $row_number, 3, $row[56],               $gray_field_format  );    # cookie name
        $worksheet->write_string( $row_number, 4, $row[57],               $gray_value_format  );    # cookie value
        $worksheet->write_string( $row_number, 6, " ",                    $gray_type_format   );    # blank
    }

    if ( $row[2] =~ /bookmark/ ){
        $worksheet->write_string( $row_number, 0, $row[2],                $red_type_format   );    # record_type
        $worksheet->write(        $row_number, 1, &EpochToExcel($row[1]), $red_date_format   );    # date
        $worksheet->write_string( $row_number, 2, "$row[59]",             $red_url_format    );    # URL
        $worksheet->write_string( $row_number, 3, $row[60],               $red_value_format  );    # bookmark name
        $worksheet->write_string( $row_number, 4, $row[61],               $red_value_format  );    # bookmark folder
        $worksheet->write_string( $row_number, 6, " ",                    $red_type_format   );    # blank
    }	
    $row_number++;
    $excel_progress->update($row_number);
}

# Formatting
$worksheet->freeze_panes( 2, 0 );            # Freeze top row
$worksheet->autofilter(1,0,$row_number,15);  # Add autofiler

#Set column widths
$worksheet->set_column( 'A:A', 16 );         # Type
$worksheet->set_column( 'B:B', 18 );         # Date
$worksheet->set_column( 'C:C', 60 );         # URL
$worksheet->set_column( 'D:D', 25 );         # Title / Name / Status
$worksheet->set_column( 'E:E', 80 );         # Data / Value / Path
$worksheet->set_column( 'F:F', 60 );         # Interpretation
$worksheet->set_column( 'G:G', 12 );         # Safe Browsing
                                          # URL Specfic
$worksheet->set_column( 'H:J', 6  );         # Visit Count, Typed Count, Hidden
$worksheet->set_column( 'K:K', 12 );         # Transition
                                          # Download Specific
$worksheet->set_column( 'L:L', 12 );         # Interrupt Reason
$worksheet->set_column( 'M:M', 24 );         # Danger Type
$worksheet->set_column( 'N:N', 12 );         # Opened
$worksheet->set_column( 'O:O', 12 );         # ETag
$worksheet->set_column( 'P:P', 27 );         # Last Modified


# Preferences Page
my $pref_worksheet = $workbook->add_worksheet('Preferences');

# Title bar
$pref_worksheet->merge_range( 'A1:E1', "Hindsight Internet History Forensics (v$version)",    $title_header_format );


#Set column widths
$pref_worksheet->set_column( 'A:A', 2 );     # Indent
$pref_worksheet->set_column( 'B:B', 4 );     # Options Header
$pref_worksheet->set_column( 'C:C', 30 );    # Setting
$pref_worksheet->set_column( 'D:D', 80 );    # Value
$pref_worksheet->set_column( 'E:E', 2 );     # Indent

# Create formats
my $option_header_format = $workbook->add_format( color => "black",  align => "left",  bottom => 1,   bg_color => 'silver'  );
my $pref_setting_format    = $workbook->add_format( color => "black",  align => "left",  valign => 'vcenter' );
my $pref_value_format    = $workbook->add_format( color => "black",  align => "left",  text_wrap => 1 );


# Select distinct options
my $options_list_h = $datastore->prepare("SELECT DISTINCT option FROM preferences");
$options_list_h->execute();

my $settings_h = $datastore->prepare("SELECT key,value option FROM preferences WHERE option = ? ");

my $pref_row = 2;

while (my @options_list = $options_list_h->fetchrow) {
	my $option = $options_list[0];
	$pref_worksheet->write( $pref_row, 1, $option, $option_header_format );
	$pref_worksheet->write_blank( $pref_row, 2, $option_header_format );
	$pref_worksheet->write_blank( $pref_row, 3, $option_header_format );
	$pref_row++;
	$settings_h->execute($option);
	while (my @setting = $settings_h->fetchrow) {
		if ($setting[0] && $setting[1]){
			unless ( $setting[1] =~ /^HASH\(0x/ ){
				$pref_worksheet->write( $pref_row, 2, $setting[0], $pref_setting_format ); 
				$pref_worksheet->write( $pref_row, 3, $setting[1], $pref_value_format );
				$pref_row++;    }
		}
	}
	$pref_row++;
}

# Print finish time
my @endTimeData = localtime(time);
my $endTime = ($endTimeData[5]+1900) . "-" . sprintf("%02s",($endTimeData[4]+1)) . "-" . sprintf("%02s",$endTimeData[3]) . " " . sprintf("%02s",$endTimeData[2]) . "-" . sprintf("%02s",$endTimeData[1]) . "-" . sprintf("%02s",$endTimeData[0]);                

print "\n\n  Finsh time: $endTime\n";

#####################################################################################
#####                          Supporting subroutines                           #####
#####################################################################################

sub WebkitToEpoch {
    my $webkit = $_[0];
	if ($webkit){
		if ($webkit < 9999999999){
			return $webkit; }
		else {
			my $epoch = ($webkit/1000000)-11644473600;
			return $epoch; }
	}
	else { return 0; }
}

sub EpochToExcel {
    my $epoch = $_[0];
    my $excel = ($epoch/86400)+25569;
    return $excel;
}
    
sub EpochToHuman {
    my $epoch = $_[0];
    if ($epoch =~ /\d{13}/)    { $epoch = $epoch/1000; }
    if ($epoch){
        my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($epoch);
        my $human = ($year+1900) . "-" . sprintf("%02d",($mon+1)) . "-" . sprintf("%02d",$mday) . "T" . sprintf("%02d",$hour) . ":" . sprintf("%02d",$min) . ":" . sprintf("%02d",$sec); 
        return $human; 
    } else {    return 0;    }
}
    
sub DecodeTransition {
    my $raw_transition = $_[0];
    my $core_mask = 0xff;
    my $transition_code = $core_mask & $raw_transition;
    if ( $transition_code < 11 && $transition_code >= 0 ){
        return $transition_code; }
    else { return 11; }
}

sub DecodeInterruptReason {
    my $interrupt = $_[0];
    my %interrupts;

    if ($interrupt == '0'){ $interrupt = 'none'}

    $interrupts{'none'} =  " ";                       # Success

    # from download_interrupt_reason_values.h on Chromium site
    # File errors.
    $interrupts{'1'} =  "File Error";                 # Generic file operation failure.
    $interrupts{'2'} =  "Access Denied";              # The file cannot be accessed due to security restrictions.
    $interrupts{'3'} =  "Disk Full";                  # There is not enough room on the drive.
    $interrupts{'5'} =  "Path Too Long";              # The directory or file name is too long.
    $interrupts{'6'} =  "File Too Large";             # The file is too large for the file system to handle.
    $interrupts{'7'} =  "Virus";                      # The file contains a virus.
    $interrupts{'10'} = "Temporary Problem";          # The file was in use. Too many files are opened at once. We have run out of memory.
    $interrupts{'11'} = "Blocked";                    # The file was blocked due to local policy.
    $interrupts{'12'} = "Security Check Failed";      # An attempt to check the safety of the download failed due to unexpected reasons. See http://crbug.com/153212.
    $interrupts{'13'} = "Resume Error";               # An attempt was made to seek past the end of a file in opening a file (as part of resuming a previously interrupted download).
    
    # Network errors.
    $interrupts{'20'} = "Network Error";              # Generic network failure.
    $interrupts{'21'} = "Operation Timed Out";        # The network operation timed out.
    $interrupts{'22'} = "Connection Lost";            # The network connection has been lost.
    $interrupts{'23'} = "Server Down";                # The server has gone down.
    
    # Server responses.
    $interrupts{'30'} = "Server Error";               # The server indicates that the operation has failed (generic).
    $interrupts{'31'} = "Range Request Error";        # The server does not support range requests. Internal use only:  must restart from the beginning.
    $interrupts{'32'} = "Server Precondition Error";  # The download request does not meet the specified precondition. Internal use only:  the file has changed on the server.
    $interrupts{'33'} = "Unable to get file";         # The server does not have the requested data.

    # User input.
    $interrupts{'40'} = "Canceled";                   # The user canceled the download.
    $interrupts{'41'} = "Browser Shutdown";           # The user shut down the browser. Internal use only:  resume pending downloads if possible.
    
    # Crash.
    $interrupts{'50'} = "Browser Crashed";            # The browser crashed. Internal use only:  resume pending downloads if possible.

    if ($interrupt){
        if ($interrupts{$interrupt}){
            return $interrupts{$interrupt}; } 
        else {
            return "[Error - Unknown Interrupt Code]"; }
    } else {
        return "[Error - No Value]"; }
}

sub DecodeDangerType {
    my $danger = $_[0];
    my %dangers;

    # from download_danger_type.h on Chromium site
    $dangers{'0'} = "Not Dangerous";                # The download is safe.
    $dangers{'1'} = "Dangerous";                    # A dangerous file to the system (e.g.: a pdf or extension from places other than gallery).
    $dangers{'2'} = "Dangerous URL";                # Safebrowsing download service shows this URL leads to malicious file download.
    $dangers{'3'} = "Dangerous Content";            # SafeBrowsing download service shows this file content as being malicious.
    $dangers{'4'} = "Content May Be Malicious";     # The content of this download may be malicious (e.g., extension is exe but SafeBrowsing has not finished checking the content).
    $dangers{'5'} = "Uncommon Content";             # SafeBrowsing download service checked the contents of the download, but didn't have enough data to determine whether it was malicious.
    $dangers{'6'} = "Dangerous But User Validated"; # The download was evaluated to be one of the other types of danger, but the user told us to go ahead anyway.
    $dangers{'7'} = "Dangerous Host";               # SafeBrowsing download service checked the contents of the download and didn't have data on this specific file, but the file was served from a host known to serve mostly malicious content.
    $dangers{'8'} = "Potentially Unwanted";         # Applications and extensions that modify browser and/or computer settings

    if ($danger=~/[0-8]/){
        if ($dangers{$danger}){
            return $dangers{$danger}; }
        else {
            return "[Error - Unknown Danger Code]"; }
    } else {
        return "[Error - No Value]"; }
}

sub DecodeDownloadState {
    my $state = $_[0];
    my %states;

    # from download_item.h on Chromium site
    $states{'0'} = "In Progress";    # Download is actively progressing.
    $states{'1'} = "Complete";       # Download is completely finished.
    $states{'2'} = "Cancelled";      # Download has been cancelled.
    $states{'3'} = "Interrupted";    # This state indicates that the download has been interrupted.

    if ($state=~/[0-3]/){
        if ($states{$state}){
            return $states{$state}; }
        else { return "[Error - Unknown State]"; }
    } else {
        return "[Error - No Value]"; }
}

sub ExtractDomain {
    my $url = $_[0];
    if ($url=~m|(\w+)://([^/:]+)(:\d+)?/(.*)|){
        my $domainName = $2;
        return $domainName;  }
    else { return 0; }
}

sub printDescription {
    my $descr = $_[0];
    my $length = length $descr;
    my $spacers = ( $left_width - $length );
    print ' ' x $spacers . "$descr";
}

sub printStatus {
    my $status = $_[0];
    my $length = length $status;
    my $total_width = 32;
    my $spacers = ( $total_width - $length - 4 ) / 2;
    print ' ' x $spacers . " - $status -\n";
}

sub help {
	print "\n Hindsight v.$version - Internet history forensics for Google Chrome/Chromium\n";
	print << 'EOT';

 This script parses the files in the Chrome data folder, runs various plugins
    against the data, and then outputs the results in an xlsx spreadsheet.

 Usage:
	hindsight.pl [-i input directory] [-o output name] [-r] [-h]               

  -i input directory ...... Chrome data directory to process
  -o output name........... Output name (will create .xlsx and .sqlite files)
  -r ...................... Allows use of remote resources (external lookups) 
  -h ...................... Show help (this message)
  
 Example:  C:\>hindsight.pl -i "C:\Users\Ryan\AppData\Local\Google\
                Chrome\User Data\Default" -o test_case -r
    
 The Chrome data folder default locations are:
    WinXP:    <userdir>\Local Settings\Application Data\Google\Chrome
                \User Data\Default\
    Vista/7:  <userdir>\AppData\Local\Google\Chrome\User Data\Default\
    Linux:    <userdir>/.config/google-chrome/Default/             
    OS X:     <userdir>/Library/Application Support/Google/Chrome/Default/
EOT
}